﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Web.Caching;

namespace JotCMS
{
    public class Jot
    {
        private const string PublishedContentColumnName = "PublishedContent";
        private const string DraftContentColumnName = "DraftContent";
        private const string CacheKeyPrefix = "JotContent_";
        private const string JotContentTable_Updated = "Updated";
        private const string JotContentTable_CreatedBy = "CreatedBy";
        private const string JotContentTable_UpdatedBy = "UpdatedBy";
        private const string JotContentTable_Created = "Created";

        public AspNetCache cache { get; set; }
        public JotMode CurrentJotMode { get; set; }
        public JotCMSConfiguration Config { get; set; }

        public Jot()
        {
            cache = new AspNetCache(HttpContext.Current.Cache);
            CurrentJotMode = JotMode.Live;
            Config = JotCMSConfiguration.GetConfig();
        }

        public JotContentItem GetJotContent(string ContentName)
        {
            return GetContentFromDB(ContentName);
        }

        private JotContentItem GetContentFromDB(string key)
        {
            JotContentItem jotContent = new JotContentItem();
            jotContent.PublishedContent = String.Format("[{0}]", key);
            jotContent.DraftContent = String.Format("[{0}]", key);

            if (CurrentJotMode != JotMode.Edit && Config.Cache && cache.Exists(key))
            {
                jotContent.PublishedContent = cache.Get<string>(CacheKeyPrefix + key);
                return jotContent;
            }
            else
            {
                using (SqlConnection cn = new SqlConnection(GetConnectionString()))
                {
                    try
                    {
                        SqlCommand cmd = new SqlCommand(ContentSQL(key), cn);

                        if(cn.State != ConnectionState.Open)
                            cn.Open();

                        if (cn.State == ConnectionState.Open)
                        {
                            SqlDataReader rdr = cmd.ExecuteReader();

                            bool RecordExists = false;
                            while (rdr.Read())
                            {
                                RecordExists = true;

                                jotContent.PublishedContent = rdr.GetString(rdr.GetOrdinal(PublishedContentColumnName));
                                jotContent.DraftContent = rdr.GetString(rdr.GetOrdinal(DraftContentColumnName));
                                jotContent.UpdatedBy = (rdr.IsDBNull(rdr.GetOrdinal(JotContentTable_UpdatedBy)) ? string.Empty : rdr.GetString(rdr.GetOrdinal(JotContentTable_UpdatedBy)));
                                jotContent.Updated = (rdr.IsDBNull(rdr.GetOrdinal(JotContentTable_Updated)) ? DateTime.MinValue : rdr.GetDateTime(rdr.GetOrdinal(JotContentTable_Updated)));
                                jotContent.CreatedBy = (rdr.IsDBNull(rdr.GetOrdinal(JotContentTable_CreatedBy)) ? string.Empty : rdr.GetString(rdr.GetOrdinal(JotContentTable_CreatedBy)));
                                jotContent.Created = (rdr.IsDBNull(rdr.GetOrdinal(JotContentTable_Created)) ? DateTime.MinValue : rdr.GetDateTime(rdr.GetOrdinal(JotContentTable_Created)));
                                jotContent.Culture = rdr.GetString(rdr.GetOrdinal("Culture"));
                            }

                            // if the content area did not exist, create it
                            if (!RecordExists)
                                CreateNewJotContentRecord(cn, key);
                            else
                            {
                                // add to cache, only if LIVE, never Edit draft stuff
                                if (CurrentJotMode == JotMode.Live && Config.Cache)
                                    cache.Add(CacheKeyPrefix + key, jotContent.PublishedContent.ToString(), Config.CacheDuration);
                            }

                            if (!rdr.IsClosed)
                                rdr.Close();

                            rdr.Dispose();
                        }
                    }
                    catch (System.Data.SqlClient.SqlException ex)
                    {
                        if (ex.Number == 208)  // table doesn't exist, provision it
                        {
                            CreateJotContentTable(cn);
                            GetContentFromDB(key);
                        }
                    }

                    return jotContent;
                }
            }
        }

        internal void SaveContent(string Content_Name, string HTMLContent)
        {
            string sql = String.Format("UPDATE JotContent Set DraftContent = '{0}', PublishedContent = '{0}', Updated = '{1}', UpdatedBy = '{2}' WHERE ContentName = '{3}'", HTMLContent, DateTime.Now, HttpContext.Current.User.Identity.Name, Content_Name);
            using (SqlConnection cn = new SqlConnection(GetConnectionString()))
            {
                ExecuteSQLCommand(cn, sql);

                // add to the cache
                if (cache.Exists(CacheKeyPrefix + Content_Name))
                    cache._cache[CacheKeyPrefix + Content_Name] = HTMLContent;
                else
                    cache.Add(CacheKeyPrefix + Content_Name, HTMLContent, Config.CacheDuration);
            }
        }

        public void RemoveCacheItem(string key)
        {
            if (cache.Exists(CacheKeyPrefix + key))
                cache.Remove(CacheKeyPrefix + key);
        }

        public void RemoveAllCacheItems()
        {
            foreach (Cache c in HttpContext.Current.Cache)
            {
                //if (c.ToString().StartsWith(CacheKeyPrefix))
                //    c.Remove();
            }
        }

        private void CreateNewJotContentRecord(SqlConnection cn, string key)
        {
            string sql = String.Format("INSERT INTO JotContent (ContentName, DraftContent, PublishedContent, Created, CreatedBy, Culture) VALUES ('{0}', '{1}','{1}', '{2}', '{3}', '{4}') ", key, String.Format("[{0}]", key), DateTime.Now, HttpContext.Current.User.Identity.Name, "en-US");
            ExecuteSQLCommand(cn, sql);
        }

        private string ContentSQL(string ContentName)
        {
            string ColumnsToReturn = "*";
            return String.Format("SELECT {0} FROM JotContent WHERE ContentName = '{1}'", ColumnsToReturn, ContentName);
        }

        private string GetConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[Config.ConnectionStringName].ToString();
        }

        private void CreateJotContentTable(SqlConnection cn)
        {
            string sql = @"CREATE TABLE [dbo].[JotContent]( [JotContent_ID] [int] IDENTITY(1,1) NOT NULL, [ContentName] [nvarchar](50) NULL, [DraftContent] [nvarchar](max) NULL, [PublishedContent] [nvarchar](max) NULL, [Created] [datetime] NOT NULL, [Updated] [datetime] NULL, [CreatedBy] [nvarchar](50) NULL, [UpdatedBy] [nvarchar](50) NULL, [Culture] [nvarchar](10) NULL, CONSTRAINT [PK_Content] PRIMARY KEY CLUSTERED  ( [JotContent_ID] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY] ) ON [PRIMARY]";

            ExecuteSQLCommand(cn, sql);
        }

        private void ExecuteSQLCommand(SqlConnection cn, string sql)
        {
            if (cn.State != ConnectionState.Open)
                cn.Open();

            if (cn.State == ConnectionState.Open)
            {
                SqlCommand cmd = new SqlCommand(sql, cn);
                cmd.ExecuteNonQuery();
            }
        }

        public enum JotMode
        {
            Edit,
            Live
        }
    }

    public class JotContentItem
    {
        public int Content_ID { get; set; }
        public string ContentName { get; set; }
        public string DraftContent { get; set; }
        public string PublishedContent { get; set; }
        public DateTime Created { get; set; }
        public DateTime Updated { get; set; }
        public string CreatedBy { get; set; }
        public string UpdatedBy { get; set; }
        public string Culture { get; set; }
    }
}